﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    class DataProvider
    {
        public static SqlConnection GetConnection()
        {
          return new SqlConnection("server=.\\SQLEXPRESS;Initial Catalog=DoAn_HQTCSDL_QuanLyThucPham;Integrated Security=True");
         
        }
        //thuc hien goi proc_name khong tham so
        public static DataTable SpSelect(string spName)
        {
            var table = new DataTable();
            var connect = GetConnection();
            connect.Open();
            var ap = new SqlDataAdapter(spName, connect) { SelectCommand = { CommandType = CommandType.StoredProcedure } };
            ap.Fill(table);
            connect.Close();
            return table;
        }
        public static DataTable SpSelectTheoThamSo(string spName, List<SqlParameter> parameters)
        {
            var table = new DataTable();
            var connect = GetConnection();
            connect.Open();
            var adapter = new SqlDataAdapter(spName, connect) { SelectCommand = { CommandType = CommandType.StoredProcedure } };
            foreach (var parameter in parameters)
                adapter.SelectCommand.Parameters.Add(parameter);
            adapter.Fill(table);
            connect.Close();
            return table;
        }

        //thuc hien goi proc co tham so co output
        public static int spInsert_Update_Test(string spName, List<SqlParameter> parameters, SqlParameter output)
        {
            var connect = GetConnection();
            connect.Open();
            var command = new SqlCommand(spName, connect) { CommandType = CommandType.StoredProcedure };
            command.Parameters.Add(output);
            foreach (var parameter in parameters)
                command.Parameters.Add(parameter);
            command.ExecuteNonQuery();
            connect.Close();
            return (int)command.Parameters[output.ParameterName].Value;
        }
        //khong co output
        public static void spDelete(string spName, SqlParameter parameter)
        {
            var connect = GetConnection();
            connect.Open();
            var command = new SqlCommand(spName, connect) { CommandType = CommandType.StoredProcedure };
            command.Parameters.Add(parameter);
            command.ExecuteNonQuery();
            connect.Close();
        }


    }
}
